package com.example.blob;

import com.example.bean.Customer;
import com.example.util.JDBCUtils;
import org.junit.Test;

import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

// blob类型数据操作
public class BlobTest {

    // 使用PreparedStatement插入blob类型的数据
    @Test
    public void testInsert() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();

            String sql = "INSERT INTO customers(name, email, birth, photo) VALUES(?, ?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, "ada");
            preparedStatement.setObject(2, "ada@123.com");
            preparedStatement.setObject(3, "1986-10-06");
            InputStream resource = Files.newInputStream(Paths.get("src/main/resources", "animal.jpg"));
            preparedStatement.setBlob(4, resource);

            preparedStatement.execute();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

    // 读取blob类型的数据
    @Test
    public void testQuery() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();

            String sql = "SELECT id, name, email, birth, photo FROM customers WHERE id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, 10);

            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                // 取字段的方式一：根据索引
                // int id = resultSet.getInt(1);
                // String name = resultSet.getString(2);
                // String email = resultSet.getString(3);
                // Date birth = resultSet.getDate(4);
                // Blob photo = resultSet.getBlob(5);

                // 取字段的方式二：根据列名
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                Date birth = resultSet.getDate("birth");
                // 读取blob类型数据
                Blob photo = resultSet.getBlob("photo");
                InputStream binaryStream = photo.getBinaryStream();
                Files.copy(binaryStream, Paths.get("src/main/resources", name + "-photo.jpg"));

                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }


}
